package com.java1234.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.stereotype.Service;

import com.java1234.model.Article;
import com.java1234.util.JdbcUtil;

@Service("articlerDao")
public class ArticleDao {
	

	public int add(Article article) throws SQLException
		{
			DataSource ds=JdbcUtil.getDataSource();
			Connection conn=ds.getConnection();
			QueryRunner qr = new QueryRunner();
			String sql = "insert into article values(?,?,?);";
			int result=qr.update(conn,sql,null,article.getTitle(),article.getContent());
			conn.close();
			if(conn.isClosed()){

			}else{

			}
			return result;
		}
	

	public void delete(String article_id) throws SQLException
		{
			QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
			String sql = "delete from  article  where id=?";
			qr.update(sql,article_id);
		}
	

	public List<Article> articleList() throws SQLException
		{
			QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
			String sql = "select * from article order by id desc";
			
			ResultSetHandler<List<Article>> rsh = new BeanListHandler<Article>(Article.class);
			List<Article> articlelist = qr.query(sql, rsh);
			
		
			return articlelist;
			
		}
	

	public List<Article> manageList(Map map) throws SQLException
		{
			QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
			String sql = "select id,title from article order by id desc limit ?,?";
			
			ResultSetHandler<List<Article>> rsh = new BeanListHandler<Article>(Article.class);
			List<Article> articlelist = qr.query(sql, rsh, map.get("start"),map.get("size"));
			
			return articlelist;
			
		}

	public Long getTotal() throws SQLException
	{
		QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
		String sql = "SELECT COUNT(*) FROM article";

		Long total =qr.query(sql,new ScalarHandler<Long>());
		
		
		
		return total;
		
	}


	public void delete(int parseInt) throws SQLException {		
			QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
			String sql = "delete  from  article  where id=?";
			qr.update(sql,parseInt);		
	}


	public Article findById(int parseInt) throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
		String sql = "select * from  article where id=?";		
		/**
		 * ResultSetHandler�ӿ�ֻ��һ��������T handle(ResultSet)
		 * BeanHandlerʵ��������ӿڣ�����ѽ�������ݷ�װ��Student������
		 */
		ResultSetHandler<Article> rsh = new BeanHandler<Article>(Article.class);
		Article article = qr.query(sql, rsh, parseInt);
		return article;
		
	}


	public int  update(Article article) throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
		String sql = "update  article set title=? , content=? where id=?";
		int result = qr.update(sql, article.getTitle(),article.getContent(),article.getId());
		return result;
	}
		
	

}
